import copy
import json
from io import BytesIO

import xlsxwriter
from urllib.parse import unquote, quote
from apis import api_routes
from flask import g, request, jsonify, abort, Response
from security.decoration import certification
from sqlalchemy import and_
from typing import List
from typing import Union
from sqlalchemy import text
from sqlalchemy import func
from public.local import date_type, local_time
from datetime import datetime, timedelta
from models.course.course import Course
from models.product.routes import Routes
from models.order.tasks import Tasks
from public.statistics import calculate_cpk
from models.product.aircraft import Aircraft
from models.course.detailed import Detailed
from models.course.sample import Sample
from models.facility.facility import Facility


@api_routes.route("/sample/product/first", methods=["GET"])
@certification(auth=True, role=None)
def func_sample_product_first_get_api():
    """
    首件报表API，完成API开发工作
    http://127.0.0.1:8080/api/sample/product/first
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"        string          开始时间, 默认今天前面30天时间  格式 %Y-%m-%d
    "finish"       string          结束时间，默认今天时间  格式 %Y-%m-%d
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 产品报表
    """
    size = request.args.get("size", default=10, type=int)
    index = request.args.get("index", default=1, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish = request.args.get("finish", default=None, type=str)

    arg0, arg1 = start, finish
    if start is None or len(start) == 0:
        arg0 = date_type() - timedelta(days=30)
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")

    if finish is None or len(finish) == 0:
        arg1 = date_type() + timedelta(days=1) - timedelta(seconds=1)
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    start: str = arg0.strftime("%Y-%m-%d %H:%M:%S")
    finish: str = arg1.strftime("%Y-%m-%d %H:%M:%S")

    sql_count: Union[str, None] = """
        SELECT
            COUNT(detailed.id)
        FROM detailed INNER JOIN tasks ON detailed.tasks_id = tasks.id 
        INNER JOIN aircraft ON detailed.aircraft_id = aircraft.id 
        WHERE detailed.first = 't' {}
    """
    sql_context: Union[str, None] = """
        SELECT
            detailed.id, tasks.orders, tasks.name, aircraft.aircraft, detailed.sequence, 
            coalesce(to_char(detailed.time,'yyyy-MM-dd hh24:MI:ss'), null) AS start,
            coalesce(to_char(detailed.finish,'yyyy-MM-dd hh24:MI:ss'), null) AS finish,
            detailed.message, detailed.status, detailed.numbers, detailed.error, 
            detailed.option, facility.name, facility.id, detailed.mark
        FROM detailed INNER JOIN tasks ON detailed.tasks_id = tasks.id 
        INNER JOIN aircraft ON detailed.aircraft_id = aircraft.id 
        INNER JOIN facility ON detailed.facility_id = facility.id 
        WHERE detailed.first = 't' {} ORDER BY detailed.id LIMIT :arg2 OFFSET :arg3
    """
    parm1 = ""
    if start is not None and finish is not None:
        parm1 = "AND detailed.time >= :arg0 AND detailed.time <= :arg1"
    sql_count = sql_count.format(parm1)
    sql_context = sql_context.format(parm1)
    parm: dict = {
        "arg0": start, "arg1": finish,
        "arg2": size, "arg3": ((index - 1) * size)
    }

    count,  = g.session.execute(text(sql_count), parm).fetchone()
    page: int = int((count / size) + 1) if (count / size) > int(count / size) else int(count / size)
    data = g.session.execute(text(sql_context), parm).fetchall()
    rsp: list = []
    probably: dict = {
        1: "合格",
        2: "不合格",
        3: "报废"
    }
    for db_id, orders, name, aircraft, seq, start, finish, message, status, nbs, error, option, ft_n, ids, mark in data:
        db_message_resp: list = []
        if message is not None:
            db_message: dict = json.loads(message)
            db_message: list = db_message.get("data")
            for itr in db_message:
                db_message_resp.append({
                    "key": itr.get("key"),
                    "value": itr.get("value"),
                    "upper": itr.get("upper"),
                    "floor": itr.get("floor"),
                })

        rsp.append({
            "detailed_id": db_id,
            "orders": orders,
            "task_name": name,
            "aircraft": aircraft,
            "sequence": seq,
            "start": start,
            "finish": finish,
            "mark": probably.get(mark),
            "option": option,
            "db_message_resp": db_message_resp,
            "numbers": nbs,
            "facility_name": ft_n
        })

    doc: dict = {
        "detailed_id": "数据库ID",
        "orders": "工单号",
        "task_name": "任务名称",
        "aircraft": "机种",
        "sequence": "产品SN",
        "start": "开始时间",
        "finish": "结束时间",
        "mark": "加工最终结果",
        "option": "操作员",
        "db_message_resp": "采集数据",
        "numbers": "加工次数",
        "facility_name": "设备名称"
    }
    return jsonify({'status': 200, 'data': rsp, "index": index, "size": size, "total": count, "pages": page, "doc": doc})


@api_routes.route("/sample/product/first", methods=["PUT"])
@certification(auth=True, role=None)
def func_sample_product_first_put_api():
    """
    更新首件报表信息API，完成API开发工作
    http://127.0.0.1:8080/api/sample/product/first
    Content-Type=application/json
    methods=["PUT"]
    {
    "detailed_id"  int      ****   数据库ID
    "db_message_resp"       array[json{key: xx, value: xxx}, json{key: xx, value: xxx}]   数据
    }
    :return:
        {'status': 4000, 'msg': "不存在此数据"}
        {'status': 4001, 'msg': "上传数据格式错误, 数据格式array格式"}
        {'status': 4002, 'msg': "上传数据格式错误, 数据格式array里面嵌套json"}
        {'status': 4003, 'msg': "上传数据格式错误, 数据格式array里面嵌套json包含key value"}
        {'status': 4004, 'msg': "上传数据格式错误, 重复上传数据"}
        {'status': 200, 'msg': "OK"}
    ---
    tags:
      - 产品报表
    """
    data: dict = request.get_json()
    detailed_id: int = data.get("detailed_id")
    data = data.get("db_message_resp")
    if detailed_id is None or data is None:
        abort(code=400)
    db_detailed: Union[Detailed, None] = g.session.query(Detailed).filter(and_(
                                            Detailed.first == "t",
                                            Detailed.id == detailed_id
                                        )).first()
    if db_detailed is None:
        return jsonify({'status': 4000, 'msg': "不存在此数据"})

    if isinstance(data, list) is False:
        return jsonify({'status': 4001, 'msg': "上传数据格式错误, 数据格式array格式"})

    chek_keys: list = []
    save_data: Union[dict, None] = None
    for itr in data:
        if isinstance(itr, dict) is False:
            return jsonify({'status': 4002, 'msg': "上传数据格式错误, 数据格式array里面嵌套json"})
        temp: dict = itr
        keys: list = list(temp.keys())
        if "key" not in keys or "value" not in keys:
            return jsonify({'status': 4003, 'msg': "上传数据格式错误, 数据格式array里面嵌套json包含key value"})
        if "floor" not in keys or "upper" not in keys:
            return jsonify({'status': 4003, 'msg': "上传数据格式错误, 数据格式array里面嵌套json包含floor upper"})
        chek_keys.append({
            "key": temp["key"],
            "value": temp["value"],
            "floor": temp["floor"],
            "upper": temp["upper"]
        })
    if len(chek_keys) > 0:
        save_data: dict = {
            "data": chek_keys
        }

    db_detailed.message = json.dumps(save_data) if save_data is not None else None
    g.session.commit()
    return jsonify({'status': 200, 'msg': "OK"})


@api_routes.route("/sample/task", methods=["GET"])
@certification(auth=True, role=None)
def func_sample_task_get_api():
    """
    查询样件工单报表API，(样件报表)
    http://127.0.0.1:8080/api/sample/task
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"        string          开始时间, 默认今天前面30天时间  格式 %Y-%m-%d
    "finish"       string          结束时间，默认今天时间  格式 %Y-%m-%d
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 产品报表
    """
    size: int = request.args.get("size", default=10, type=int)
    index: int = request.args.get("index", default=1, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)

    arg0, arg1 = start, finish
    if finish is None or len(finish) == 0:
        arg1 = date_type() + timedelta(days=1) - timedelta(seconds=1)
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    if start is None or len(start) == 0:
        arg0 = date_type() - timedelta(days=30)
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")

    parameter: list = []
    if arg0 is not None:
        parameter.append(Sample.time >= arg0)
    if arg1 is not None:
        parameter.append(Sample.finish <= arg1)

    count = g.session.query(Sample.id).filter(* parameter).count()
    rsp: list = []
    data: List[Sample] = g.session.query(Sample).filter(* parameter).order_by(Sample.id).offset((index - 1) * size).limit(size).all()

    page: int = int((count / size) + 1) if (count / size) > int(count / size) else int(count / size)
    for _, itr in enumerate(data):
        db_message_resp: list = []
        if itr.message is not None:
            db_message: dict = json.loads(itr.message)
            db_message: list = db_message.get("data")
            for itr2 in db_message:
                db_message_resp.append({
                    "key": itr2.get("key"),
                    "upper": itr2.get("upper"),
                    "floor": itr2.get("floor"),
                    "value": itr2.get("value"),
                })
        db_facility: Facility = g.session.query(Facility).filter(Facility.id == itr.facility_id).first()

        rsp.append({
            "sample_id": itr.id,
            "sequence": itr.sequence,
            "facility_name": db_facility.name,
            "time": itr.time.strftime("%Y-%m-%d %H:%M:%S"),
            "finish": itr.finish.strftime("%Y-%m-%d %H:%M:%S"),
            "db_message_resp": db_message_resp,
            "option": itr.option,
            "status_mes": itr.status_mes,
            "status_plc": itr.status_plc
        })

    doc: dict = {
        "sample_id": "数据库ID",
        "sequence": "产品编号",
        "facility_name": "设备名称",
        "time": "生产时间",
        "finish": "完工时间",
        "db_message_resp": "采集信息",
        "option": "操作用户",
        "status_mes": "MES判断是否合格",
        "status_plc": "PLC判断是否合格"
    }
    return jsonify({'status': 200, 'data': rsp, "index": index, "size": size, "total": count, "page": page, "doc": doc})


@api_routes.route("/sample/task/output", methods=["GET"])
def func_sample_task_output_get_api():
    """
    查询样件工单报表API，(样件报表), 导出EXCEL
    http://127.0.0.1:8080/api/sample/task/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"        string          开始时间, 默认今天前面30天时间  格式 %Y-%m-%d
    "finish"       string          结束时间，默认今天时间  格式 %Y-%m-%d
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 产品报表
    """
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)

    arg0, arg1 = start, finish
    if finish is None or len(finish) == 0:
        arg1 = date_type() + timedelta(days=1) - timedelta(seconds=1)
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    if start is None or len(start) == 0:
        arg0 = date_type() - timedelta(days=30)
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")

    parameter: list = []
    if arg0 is not None:
        parameter.append(Sample.time >= arg0)
    if arg1 is not None:
        parameter.append(Sample.finish <= arg1)

    data: List[Sample] = g.session.query(Sample).filter(*parameter).order_by(Sample.id).offset(0).limit(2048).all()

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("样件测试数据")
    worksheet_index.set_column("A:ZZ", 24)

    message: list = ["设备名称", "样件时间", "样件SN码", "操作用户", "测试结果", "测试项名称", "测试项下限", "测试项值", "测试项上限"]
    worksheet_index.write_row(row=0, col=0, data=message)

    start_line = 1
    for index, itr in enumerate(data):
        db_facility: Facility = g.session.query(Facility).filter(Facility.id == itr.facility_id).first()
        temp_sb = [db_facility.name, itr.time.strftime("%Y-%m-%d %H:%M:%S"), itr.sequence, itr.option, itr.status_plc]
        if itr.message is not None:
            xs_user: dict = json.loads(itr.message)
            xs_array: dict = xs_user.get("data")
            for xxvv in xs_array:
                copy_date_xl = copy.deepcopy(temp_sb)
                copy_date_xl.append(xxvv.get("key"))
                copy_date_xl.append(xxvv.get("floor"))
                copy_date_xl.append(str(xxvv.get("value")))
                copy_date_xl.append(xxvv.get("upper"))
                worksheet_index.write_row(row=start_line, col=0, data=copy_date_xl)
                start_line = start_line + 1
        else:
            worksheet_index.write_row(row=start_line, col=0, data=temp_sb)
            start_line = start_line + 1

    file_name = "样件数据{}.xlsx".format(local_time())

    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200


@api_routes.route("/sample/task", methods=["PUT"])
@certification(auth=True, role=None)
def func_sample_task_put_api():
    """
    查询样件工单报表API，(样件报表)
    http://127.0.0.1:8080/api/sample/task
    Content-Type=application/json
    methods=["PUT"]
    {
    "sample_id"  int      ****   数据库ID
    "status_plc"     bool   ***
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 产品报表
    """
    data: dict = request.get_json()
    sample_id: int = data.get("sample_id")
    value: bool = data.get("status_plc")
    if sample_id is None or value is None:
        abort(code=400)
    db_sample: Union[Sample, None] = g.session.query(Sample).filter(Sample.id == sample_id).first()
    if db_sample is None:
        return jsonify({'status': 4000, 'msg': "不存在此数据"})

    db_sample.status_plc = value
    g.session.commit()
    return jsonify({'status': 200})


@api_routes.route("/sample/task", methods=["POST"])
@certification(auth=True, role=None)
def func_sample_task_post_api():
    """
    下发下料机可以生产信号，(样件报表)
    http://127.0.0.1:8080/api/sample/task
    Content-Type=application/x-www-form-urlencoded
    methods=["POST"]
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 产品报表
    """

    value: Union[str, None] = g.cache.get("DM8196{}".format("c76d1cf3eb104095a6634e7835425a84"))

    if value is None:
        return jsonify({'status': 4000, 'msg': "通讯异常，获取状态失败"})
    if value == "2":
        return jsonify({'status': 4000, 'msg': "样件生产中, 请先停止样件生产"})

    uuid4: Union[str, None] = None
    db_fs: List[Facility] = g.session.query(Facility).filter(and_(
        Facility.id != 7, Facility.uuid4 != uuid4
    )).all()

    for fs in db_fs:
        # redis_running_key: str = "running{}".format(str(fs.uuid4).replace("-", ""))
        # running_value = g.cache.get(redis_running_key)
        uuid_key = "queues{}".format(str(fs.uuid4).replace("-", ""))

        g.cache.rpush(uuid_key, json.dumps({"address": "DM8196", "type": "Short", "value": 1}))
        g.cache.expire(uuid_key, 8)

    return jsonify({'status': 200})
